using System;
using System.Data;
using System.Data.OleDb;
using PCSComUtils.Common;
using PCSComUtils.DataAccess;
using PCSComUtils.PCSExc;
using System.Text;

namespace PCSComProduction.WorkOrder.DS
{
    /// <summary>
    /// Summary description for ApprovalWorkOrderCompletionDS.
    /// </summary>
    public class ApprovalWorkOrderCompletionDS
    {
        private const string THIS = "PCSComProduction.WorkOrder.DS.PRO_WorkOrderDetailDS";
        private const string WOD = " WorkOrderDetail";
        private const string WOM = " WorkOrderMaster";
        private const string PRO = " Product";
        private const string UM = " UnitOfMeasure ";
        private const string OPENQUANTITY = " OpenQuantity ";
        private const string PARTNUMBER = " PartNumber ";
        private const string PARTNAME = " PartName ";
        private const string CAPTION_UM = " UM ";
        private const string MODEL = " Model ";
        private const string SELECTED = " Selected";
        public ApprovalWorkOrderCompletionDS()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        public DataSet SearchApprovalWOCompletion(int pintCcn, int intQaStatus, int pintWoMasterID, int intProLineId, DateTime fromStartDate, DateTime toStartDate, string strFGoodsCode, int intProductId)
        {
            const string METHOD_NAME = THIS + ".SearchApprovalWOCompletion()";
            //const string ONE = "1";
            DataSet dstPCS = new DataSet();

            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                StringBuilder strSql = new StringBuilder();
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql.ToString(), oconPCS);

                strSql.Append("SELECT DISTINCT ");
                strSql.Append(" '' AS " + SELECTED + ", ");
                strSql.Append("S." + SO_SaleOrderDetailTable.FGOODSCODE_FLD + ",");
                strSql.Append("PRO_ProductionLine.Code ProductionLine, ");
                strSql.Append("WOM." + PRO_WorkOrderMasterTable.WORKORDERNO_FLD + ", ");
                strSql.Append("WOC." + PRO_WorkOrderCompletionTable.WORKORDERCOMPLETIONID_FLD + ", ");
                strSql.Append("WOC." + PRO_WorkOrderCompletionTable.CCNID_FLD + ", ");
                strSql.Append("WOC." + PRO_WorkOrderCompletionTable.WORKORDERMASTERID_FLD + ", ");
                strSql.Append("WOC." + PRO_WorkOrderCompletionTable.MASTERLOCATIONID_FLD + ", ");
                strSql.Append("WOC." + PRO_WorkOrderCompletionTable.LOCATIONID_FLD + ", ");
                strSql.Append("WOC." + PRO_WorkOrderCompletionTable.BINID_FLD + ", ");
                strSql.Append("WOD." + PRO_WorkOrderDetailTable.LINE_FLD + ",");
                strSql.Append("WOC." + PRO_WorkOrderCompletionTable.QASTATUS_FLD + ",");
                strSql.Append("WOC." + PRO_WorkOrderCompletionTable.REMARK_FLD + ",");
                strSql.Append("WOD." + PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + ",");
                strSql.Append("PRO." + ITM_ProductTable.CODE_FLD + PARTNUMBER + ", ");
                strSql.Append("PRO." + ITM_ProductTable.PRODUCTID_FLD + ", ");
                strSql.Append("PRO." + ITM_ProductTable.DESCRIPTION_FLD + PARTNAME + ", ");
                strSql.Append("PRO." + ITM_ProductTable.REVISION_FLD + MODEL + ", ");
                strSql.Append("UM." + MST_UnitOfMeasureTable.CODE_FLD + " UM " + ", ");
                strSql.Append("WOC." + PRO_WorkOrderCompletionTable.COMPLETEDQUANTITY_FLD + ", ");
                strSql.Append("WOC." + PRO_WorkOrderCompletionTable.NGQUANTITY_FLD + ", ");
                strSql.Append("WOD." + PRO_WorkOrderDetailTable.STARTDATE_FLD + ", ");
                strSql.Append("WOD." + PRO_WorkOrderDetailTable.DUEDATE_FLD);
                strSql.Append(" FROM " + PRO_WorkOrderCompletionTable.TABLE_NAME + " WOC ");
                strSql.Append(" INNER JOIN " + PRO_WorkOrderDetailTable.TABLE_NAME + " WOD ");
                strSql.Append(" ON WOC." + PRO_WorkOrderCompletionTable.WORKORDERDETAILID_FLD + " = ");
                strSql.Append("WOD." + PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD);
                strSql.Append(" INNER JOIN " + PRO_WorkOrderMasterTable.TABLE_NAME + " WOM ");
                strSql.Append(" ON WOC." + PRO_WorkOrderCompletionTable.WORKORDERMASTERID_FLD + " = ");
                strSql.Append("WOM." + PRO_WorkOrderMasterTable.WORKORDERMASTERID_FLD);
                strSql.Append(" LEFT JOIN " + SO_SaleOrderDetailTable.TABLE_NAME + " S ");
                strSql.Append(" ON WOD." + PRO_WorkOrderDetailTable.SALEORDERDETAILID_FLD + " = ");
                strSql.Append("S." + SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD);
                strSql.Append(" INNER JOIN " + ITM_ProductTable.TABLE_NAME + " PRO ");
                strSql.Append(" ON WOD." + PRO_WorkOrderDetailTable.PRODUCTID_FLD + " = ");
                strSql.Append("PRO." + ITM_ProductTable.PRODUCTID_FLD);
                strSql.Append(" INNER JOIN " + MST_UnitOfMeasureTable.TABLE_NAME + " UM ");
                strSql.Append(" ON WOD." + PRO_WorkOrderDetailTable.STOCKUMID_FLD + " = ");
                strSql.Append("UM." + MST_UnitOfMeasureTable.UNITOFMEASUREID_FLD);
                strSql.Append(" Left Join PRO_ProductionLine on PRO_ProductionLine.ProductionLineID = " + " WOD.ProductionLineID ");
                strSql.Append(" WHERE WOM." + PRO_WorkOrderCompletionTable.CCNID_FLD + " = " + pintCcn);
                strSql.Append(" AND WOC." + PRO_WorkOrderCompletionTable.QASTATUS_FLD + " = " + intQaStatus.ToString());
                strSql.Append(" AND PRO." + ITM_ProductTable.QASTATUS_FLD + " = " + ((int)EnumQA.REQUIRE_INSPECTION).ToString()); // Product must Require inspection

                // WorkOrderMasterID
                if (pintWoMasterID > 0)
                    strSql.Append(" AND WOM." + PRO_WorkOrderMasterTable.WORKORDERMASTERID_FLD + " = " + pintWoMasterID.ToString());
                // FGoodsCode
                if (strFGoodsCode != string.Empty)
                    strSql.Append(" AND S." + SO_SaleOrderDetailTable.FGOODSCODE_FLD + " = '" + strFGoodsCode + "'");

                // ProductID
                if (intProductId > 0)
                    strSql.Append(" AND WOC." + PRO_WorkOrderCompletionTable.PRODUCTID_FLD + " = " + intProductId.ToString());

                // FromStartDate
                if (fromStartDate > DateTime.MinValue)
                {
                    strSql.Append(" AND WOD." + PRO_WorkOrderDetailTable.STARTDATE_FLD + " >= ?");
                    ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.STARTDATE_FLD, OleDbType.Date));
                    ocmdPCS.Parameters[PRO_WorkOrderDetailTable.STARTDATE_FLD].Value = fromStartDate;
                }
                // ToStartDate
                if (toStartDate > DateTime.MinValue)
                {
                    strSql.Append(" AND WOD." + PRO_WorkOrderDetailTable.STARTDATE_FLD + " <= ?");
                    ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.DUEDATE_FLD, OleDbType.Date));
                    ocmdPCS.Parameters[PRO_WorkOrderDetailTable.DUEDATE_FLD].Value = toStartDate;
                }
                //ProLineID
                if (intProLineId > 0)
                    strSql.Append(" AND PRO_ProductionLine.ProductionLineID = " + intProLineId);
                //Order by
                strSql.Append(" ORDER BY Productionline, WorkOrderNo, Line, StartDate, WOD.DueDate");
                ocmdPCS.Connection.Open();
                ocmdPCS.CommandText = strSql.ToString();
                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, PRO_WorkOrderDetailTable.TABLE_NAME);

                return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }
    }
}
